"""
py-opengauss <https://gitee.com/wjmcat/py-opengauss>

Authors:    Jingjing WU (吴京京) <https://gitee.com/wjmcat/>

2021-now @ Jingjing WU (吴京京)

Licensed under the Apache License, Version 2.0 (the 'License');
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an 'AS IS' BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
"""
from __future__ import annotations
import os
import sys
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))

from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from examples import get_logger


logger = get_logger()

# 创建对象的基类:
Base = declarative_base()


def get_engine():
    # 初始化数据库连接:
    env = os.environ
    params = {
        'database': env.get('OG_DATABASE', 'opengauss'),
        'user': env.get('OG_USER', 'gaussdb'),
        'password': env.get('OG_PASSWORD', 'Secretpassword@123'),
        'host': env.get('OG_HOST', '127.0.0.1'),
        'port': env.get('OG_PORT', 5432)
    }
    url = 'postgresql://{}:{}@{}:{}/{}'
    url = url.format(
        params['user'],
        params['password'],
        params['host'],
        params['port'],
        params['database']
    )
    print(url)
    engine = create_engine(url, client_encoding='utf8')
    return engine


class SessionManager:
    _session_single_instance = None

    @classmethod
    def instance(cls):
        if not cls._session_single_instance:
            engine = get_engine()
            Base.metadata.create_all(engine)
            # 创建DBSession类型:
            DBSession = sessionmaker(bind=engine)            
            cls._session_single_instance = DBSession()
        
        return cls._session_single_instance


class Student(Base):
    """define the Student table object
    """
    __tablename__ = 'test_sqlalchemy_student'

    # the fields of the table
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(40))
    course = Column(String(40))
    grade = Integer()


def add_student(student: Student) -> int:
    """add student data to the database

    Args:
        student (Student): the student object saved to the database

    Returns:
        int: the primary key of the saved student data
    """
    session = SessionManager.instance()
    session.add(student)
    session.commit()
    session.flush()
    return student.id

def remove_stuent(student: Student):
    """delete student data from the database
    """
    session = SessionManager.instance()
    session.delete(student)
    session.commit()

def filter():
    """filter data

    Returns:
        [type]: the query object of the database
    """
    session = SessionManager.instance()
    query = session.query(Student)
    return query


if __name__ == '__main__':
    # 1. init the database
    engine = get_engine()
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

    # 2. add a student
    student = Student(name='wjmcat', course='bupt', grade=99)
    add_student(student)

    # 3. get all student data
    students = filter().all()
    assert len(students) == 1

    # 4. update student data
    student.name = 'wj-mcat'
    session = SessionManager.instance()
    session.commit()

    # 5. check the student count and name
    students = filter().all()
    assert len(students) == 1
    assert students[0].name == 'wj-mcat'

    # 6. remove the student
    remove_stuent(student)
    students = filter().all()
    assert len(students) == 0